Creating A Transformation Script

A transformation script allows the customization of import operations using a robust programming interface to introduce advanced logic.

This example presumes that a sample file for transformation is available - the file is called K_TestFile1.txt and is in C:\Temp. The file contains column headers, be quote qualified and comma delimited.

Procedure

1. Launch the Kettle ETL tool. Select File > New > Transformation, and save with an appropriate name.
2. Right-click in the Transformation job and select Transformation settings to set some initial parameters that are required to define the location of the file that will be acted upon.
3. Set the following in the Parameters tab:

    

   

   The use of the two sdl.creativeui parameters prompt the user to enter a file name when the Job is exposed via the Campaign Manager UI.

4. Click OK.
5. From the left-hand menu, select Input/ CSV file input and drag this item into the script.
6. Double-click the CSV file input and in the file name box, use CTRL-Space to show the available parameters and construct the following as ${sdl.datafile.directory}\${sdl.datafile.name}
7. Click Get Fields to interrogate the file, retrieves the column header, and populate the grid.
8. Adjust the grid as required.
9. Click OK to close.
10. From the left-hand menu, select Output/Text file Output and drag this item into the script
11. Double-click the Text file Output and in the filename section use the CTRL-space method to build the path ${sdl.datafile.directory}\${sdl.datafile.name}_working
12. Select the Content tab. In the separator section remove the existing value of a semi-colon (;) and add a pipe (|) character. Remove the quote in the enclosure section and click OK.

13. Connect these two items, from CSV File Input to Text file output using the CTRL and middle mouse button, with a drag operation creating the connection. Select Main output to step.

14. To test the transformation, use the green play button and select launch.
15. To confirm the _working file has been created, browse to C:\Temp and open the file to confirm it is now pipe delimited and quote qualified.
16. Close the confirmation script.

• Creating a Kettle script
  • Creating a Transformation Script
  • Job Script